﻿Imports System.Data.OleDb
Public Class receipedetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        '**here goes - this section is supposed to fill the label/s**'
        Dim rdrSql As OleDbDataReader = DirectCast(RecipeData.Select(DataSourceSelectArguments.Empty), OleDbDataReader)
        While rdrSql.Read()

            Recipe_LBL.Text = rdrSql("RecipeName").ToString()
            MealType_LBL.Text = rdrSql("MealType").ToString()
            PrepTime_LBL.Text = rdrSql("Preparation_Time").ToString()
            Description_LBL.Text = rdrSql("Description").ToString()
            Instructions_LBL.Text = rdrSql("Instructions").ToString()
        End While
        rdrSql.Close()


        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("list.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
            oleDbConn.Open()

            Dim recipeViewedSql As String = "INSERT into RecipesViewed (Member, Recipe_FKID) VALUES (@f1, @f2)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(recipeViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()
            oleDbConn.Close()


            Dim thisConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
            thisConn.Open()
            Dim likedSql As String = "SELECT * FROM Likes WHERE RecipeID=@f1 AND Liker=@f2"

            Dim likeCheckcmd As OleDbCommand = New OleDbCommand(likedSql, thisConn)
            likeCheckcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            likeCheckcmd.Parameters.AddWithValue("@f2", User.Identity.Name)
            Dim dr As OleDbDataReader = likeCheckcmd.ExecuteReader()

            If (dr.HasRows()) Then
                BTN_like.Visible = False
                LabelLike.Visible = True
            Else
                BTN_like.Visible = True
                LabelLike.Visible = False

            End If
            thisConn.Close()
        End If

    End Sub

    Protected Sub RecipeData_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)

        Dim thisID As Integer
        thisID = Convert.ToInt32(Context.Request.QueryString("ID"))
        e.Command.Parameters("ID").Value = thisID

    End Sub
    Protected Sub BTN_AddComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BTN_AddComment.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        '** add rating to the new separate ratings table - the previous version will be removed once workingin**'
        oleDbConn.Open()

        Dim delRating As String = "DELETE FROM RecipeRatings WHERE RecipeID=@f1 AND SiteMember=@f2"
        Dim delRatingsCmd As OleDbCommand = New OleDbCommand(delRating, oleDbConn)
        delRatingsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        delRatingsCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        delRatingsCmd.ExecuteNonQuery()

        Dim addRatings As String = "INSERT INTO RecipeRatings (RecipeID, SiteMember,Rating) Values (@f1,@f2,@f3)"
        Dim ratingsCmd As OleDbCommand = New OleDbCommand(addRatings, oleDbConn)
        ratingsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        ratingsCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        ratingsCmd.Parameters.AddWithValue("@f3", ratingvalue.Value)
        ratingsCmd.ExecuteNonQuery()


        '** now a way to change the recipe's star rating **'
        '** and total ratings - then we need to add the stars added and an extra rating to the d/base**'
        Dim thisID As Integer
        thisID = Convert.ToInt32(Context.Request.QueryString("ID"))
        Dim thisString As String = "SELECT RatingsTotal,TotalRatings, StarRating from recipes WHERE ID =" & thisID
        Dim thiscmd As OleDbCommand = New OleDbCommand(thisString, oleDbConn)
        '**this is the untested section**'
        Dim result1 As Integer
        Dim result2 As Integer
        Dim result3 As Integer

        Dim thisreader = thiscmd.ExecuteReader()

        While thisreader.Read
            result1 = thisreader.Item("RatingsTotal")
            result2 = thisreader.Item("TotalRatings")
            result3 = thisreader.Item("StarRating")
        End While
        '** it worked! the next bit jst tried a calc for testing but now we can create new figures and send them back to the database**'
        Dim newRating As Integer
        If result2 = 0 Then
            '** here I'm chaing DropDownList1.SelectedValue to the hidden value on the page**'
            newRating = ratingvalue.Value

        Else
            newRating = (result1 + ratingvalue.Value) / (result2 + 1)
        End If

        '**now insert updated star rating into the database - as the calc is based upon integers results are rounded down**'
        '** as there are currently no "half star" ratings **'

        Dim starString As String = "UPDATE recipes SET RatingsTotal =@f5,TotalRatings=@f6, StarRating=@f7 WHERE ID =" & thisID
        Dim starCmd As OleDbCommand = New OleDbCommand(starString, oleDbConn)
        starCmd.CommandType = CommandType.Text
        starCmd.Parameters.AddWithValue("@f5", result1 + ratingvalue.Value)
        starCmd.Parameters.AddWithValue("@f6", result2 + 1)
        starCmd.Parameters.AddWithValue("@f7", newRating)
        starCmd.ExecuteNonQuery()


        '**now update comments**'


        Dim SqlString As String = "Insert into Comments(Author, Comment, Title, Recipe_fkid) Values (@f1,@f2,@f3,@f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", TB_Comment.Text)
        cmd.Parameters.AddWithValue("@f3", TB_Title.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))

        cmd.ExecuteNonQuery()
        '****now fill the gridview AFTER the user has entered comments***
        Dim fillComments As String = "SELECT * FROM Comments WHERE Recipe_fkID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        ListView1.DataSourceID = ""
        ListView1.DataSource = dataReader
        ListView1.DataBind()
        '**way to get rid of the text after filling**

        TB_Comment.Text = ""
        TB_Title.Text = ""

        oleDbConn.Close()







    End Sub

    

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim SqlString As String = "Insert into RecipeTagJunction (Recipe_Fkid, Tag_Fkid) VALUES(@f1,@f2)"
        'we need to open the database connection'
        oleDbConn.Open()

        For Each cb As ListItem In CheckBoxList1.Items
            If cb.Selected Then
                'here we add the join to the table of tags and recipes'
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                'the line below request the id of this particular recipe from the query string that was used'
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmd.Parameters.AddWithValue("@f2", cb.Value)
                cmd.ExecuteNonQuery()

            End If
        Next

        If TagBox.Text = "" Then Return
        Dim newTags = TagBox.Text.Split(",")
        For Each newTag In newTags
            'first add the new tag to the tag table'
            Dim newTagSql = "INSERT INTO Tags(Tag) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'then find the id of the new tag - this isn't robust'
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from Tags ORDER BY ID DESC", oleDbConn)
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            'Associate this last ID with the hotel in the hoteltagJunction table'

            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            newcmd.CommandType = CommandType.Text
            'the line below request the id of this particular recipe from the query string that was used'
            newcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            newcmd.Parameters.AddWithValue("@f2", lastId)
            newcmd.ExecuteNonQuery()
        Next


    End Sub

    Protected Function GetImagePath() As String
        '** this section locates the image file path from the database so that the image can be show outside of gridview**'

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim value As String
        value = Convert.ToString(Context.Request.QueryString("ID"))
        Dim SqlString As String = "SELECT PICTURE from recipes WHERE ID=" & value
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        oleDbConn.Open()
        Dim imagepath = cmd.ExecuteScalar()
        Dim imageUrl = "/" + imagepath + ""

        Return imagepath

    End Function



    
    Protected Sub BTN_like_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BTN_like.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        oleDbConn.Open()
        Dim likeSql As String = "INSERT into Likes(RecipeID,Liker) VALUES(@f1,@f2)"
        Dim addLikescmd As OleDbCommand = New OleDbCommand(likeSql, oleDbConn)
        addLikescmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        addLikescmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        addLikescmd.ExecuteNonQuery()
        oleDbConn.Close()
        BTN_like.Visible = False
        LabelLike.Visible = True

    End Sub
End Class